package com.gack.business.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.gack.business.model.Enterprise;
import com.gack.business.vo.EnterpriseFrameworkVO;

@Repository
public interface EnterpriseRepository extends JpaRepository<Enterprise, String>, JpaSpecificationExecutor<Enterprise>{
	
	@Query(value = "select count(e.id), 'registered'"
			+ " from enterprise e"
			+ " union"
			+ " select count(e.id), 'certification'"
			+ " from enterprise e"
			+ " where e.status='1'"
			+ " union"
			+ " select count(e.id), 'ucertification'"
			+ " from enterprise e"
			+ " where e.status='0' or e.status='2' or e.status='3'"
			+ " union"
			+ " select count(e.id), 'dissolve'"
			+ " from enterprise e"
			+ " where e.status='4'"
			+ " union"
			+ " select count(bcr.pay_amount), 'payAmount'"
			+ " from BusinessCardRecord bcr"
			+ " where bcr.pay=1", nativeQuery = true)
	List<Object[]> findStatistics();//查询目前注册公司数量,已认证公司数量,未认证公司数量,已解散数量,共计消费金额
	
	@Query("select count(id)"
			+ " from Enterprise"
			+ " where status=:status")
	int countEnterpriseNumByStatus(@Param("status") String status);//根据状态,查询相应公司数量
	
	@Query("select new com.gack.business.vo.EnterpriseFrameworkVO(e.id, e.name, e.currentNumber)"
			+ " from Enterprise e"
			+ " where e.id=:enterpriseId")
	EnterpriseFrameworkVO findEnterpriseFrameworkVO(@Param("enterpriseId") String enterpriseId);//根据公司id获取公司架构对象 
	
	@Query("select count(uedp.userId)"
			+ " from UserEnterpriseDepartmentPosition uedp"
			+ " where uedp.enterpriseId=:enterpriseId"
			+ " and (uedp.departmentId = null or uedp.departmentId = '') ")
	int countNoDepartmentNumByEnterpriseId(@Param("enterpriseId") String enterpriseId);//获取某公司无部门人员数量
	
	@Query("select surplusAmount"
			+ " from Enterprise"
			+ " where id=:enterpriseId")
	int findSurplusAmountByEnterpriseId(@Param("enterpriseId") String enterpriseId);//获取公司账户余额
	
	@Modifying
	@Query("update Enterprise set status='1',certificationSuccessTime=:date where id=:enterpriseId")
	int updateEnterpriseStatusToOne(@Param("enterpriseId") String enterpriseId, @Param("date")Date date);//修改公司状态为'1',即已认证状态
	
	@Modifying
	@Query("update Enterprise set status='3', rejectingApprovalReason=:refuseReason, certification_defeat_time=:date  where id=:enterpriseId")
	int updateEnterpriseStatusToThree(@Param("enterpriseId") String enterpriseId, @Param("refuseReason") String refuseReason, @Param("date")Date date);//修改公司状态为'3',即认证被拒绝状态
	
	@Query("select status from Enterprise where id=:enterpriseId")
	String findStatusByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询某个公司的状态
}
